package dblayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modellayer.Customer;
import modellayer.Invoice;
import modellayer.Order;
import modellayer.PartOrder;


public class OrderDB implements IFOrderDB {
	private Connection con;
	private InvoiceDB inv;
	
	public OrderDB() {
		con = DBConnection.getInstance().getDBcon();
		inv = new InvoiceDB();
	}
	
	@Override
	public int insertOrder(Order orderObj) throws Exception {
		int rc = -1;
		

		int invoiceNoGot = inv.insertInvoice(orderObj.getInvoiceObj());
		
		//System.out.println("IDENITY TEST: "+invoiceNoGot);
		
		String query = "INSERT INTO \"Order\" (date, amount, deliveryStatus, deliveryDate, customerNo, invoiceNo) VALUES ('"+orderObj.getDate()+"', '"+orderObj.getAmount()+"', '"+orderObj.getDeliveryStatus()+"', '"+orderObj.getDeliveryDate()+"', '"+orderObj.getCustomerObj().getCustomerNo()+"', '"+invoiceNoGot+"');";
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch(SQLException ex) {
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
			
			throw new Exception("Failed to insert Order to Database!");
		}
		
		return rc;
	}
	
	@Override
	public Order findOrderByOrderNo(int orderNo) {
		String wClause = " orderNo='"+orderNo+"'";
		return singleWhere(wClause);
	}
	
	private Order singleWhere(String wClause) {
		ResultSet results;
		Order orderObj = null;
		
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if (results.next()) {
				orderObj = buildOrder(results);
				//stmt.close(); ??
			} else {
				orderObj = null;
			}
			
			stmt.close();
		} catch (Exception ex) {
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return orderObj;
	}

	private ArrayList<Order> miscWhere(String wClause)
	{
        ResultSet results;
	    ArrayList<Order> list = new ArrayList<Order>();	
		
	    String query =  buildQuery(wClause);
  
            try{ 
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
			Order orderObj = buildOrder(results);	
                 list.add(orderObj);
         		
		}
                 stmt.close();     
                 
		}
	 	catch(Exception e){
	 		
			e.printStackTrace();
	 	}
		return list;
	}
	
	private String buildQuery(String wClause) {
		String query = "SELECT orderNo, date, amount, deliveryStatus, deliveryDate, customerNo, invoiceNo FROM \"Order\"";
		
		if (!wClause.isEmpty()) {
			query += " WHERE " + wClause;
		}
		
		return query;
	}
	
	private Order buildOrder(ResultSet result) {
		
		PartOrderDB db = new PartOrderDB();
		
		Order orderObj = null;
		try {
			int orderNo = result.getInt("orderNo");
			
			ArrayList<PartOrder> partOrder = db.getPartOrdersToOrder(orderNo);
			
			orderObj = new Order(orderNo, result.getString("date"), result.getDouble("amount"), result.getString("deliveryStatus"), result.getString("deliveryDate"), new Customer(result.getInt("customerNo")), partOrder, new Invoice(result.getInt("invoiceNo")));
		} catch (SQLException e) {
			System.out.println("Error building Order object!\nError: " + e);
		}
		
		
		return orderObj;
	}


}
